{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 实例9：用Python自动生成Excel档每日出货清单"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "公司研发部门每年都需要向税务局提交一份出货清单，以申报研发费用。要求按日期来汇总，每日只要有出货，就需要一份出货单。出货总表包含数百条出货记录。假设一年有200天都出过货，那按照常规的方法，需要从总表中复制每天的出货记录，然后粘贴到每日出货清单里面，重复200次。效率低下不说，还容易出错。\n",
    "\n",
    "下面我们就让Python来代劳，一次写码，终身受益，呲牙......\n",
    "\n",
    "总表和模板分别长这样的：\n",
    "![](images\\rawdata_template.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#1.从总表中提取信息\n",
    "from openpyxl import load_workbook\n",
    "wb = load_workbook(\"data\\总表.xlsx\")\n",
    "ws= wb['Sheet']\n",
    "data = {} #用于储存提取的信息\n",
    "for row in range(2, ws.max_row+1): #从第2行开始（第1行是标题）遍历工作表每一行，将数据提取出来\n",
    "    customer = ws['B' + str(row)].value #B列为客户信息\n",
    "    model= ws['C' + str(row)].value #C列为型号\n",
    "    PN= ws['F' + str(row)].value #F列为零件号\n",
    "    qty= ws['G' + str(row)].value #G列为数量\n",
    "    date = ws['D' + str(row)].value.date() #D列为日期时间，因只要日期，不要时间，所以用date()只提取日期\n",
    "    info_list=[customer,model,PN,qty] #将以上信息放入列表info_list\n",
    "    data.setdefault(date,[]) #data字典将以日期date作为键，当天的所有产品信息组成的列表嵌套列表作为值\n",
    "    data[date].append(info_list) #将单个产品信息的列表放入包含所有产品的大列表"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们先从总表中提取信息，放到一个字典里面，方便写入Excel工作表时调用。导入`openpyxl`模块，用于打开并读取总表里面的信息。使用`load_workbook`打开总表，存入变量`wb`，然后选择工作表`Sheet`，存入变量`ws`。建立空字典`data`，用于存储数据。一开始，我们需要构思好数据结构。由于我们需要将每天的所有出货项目填入一页表，所以要用日期作为字典的键。字典的值就是产品的4个信息，即客户、型号、零件号和数量。有时候，一天只有一条出货信息，但有时有两个即以上，所以我们需要使用嵌套列表来作为字典的值。\n",
    "![](images\\data_structure.png)\n",
    "然后我们使用`for`循环遍历总表，从第二行开始直到最后一行。因为range(a,b)是取不到b的，所以需要ws.max_row+1。每读取一行，我们就将客户信息，型号，零件号和数量存入info_list里面。`data.setdefault(date,[])`是用于将日期作为键，且在遍历到具有相同日期的产品信息的时候，不覆盖原来的键（日期），而是将其值添加到后面的空列表内，即嵌套列表。\n",
    "\n",
    "数据搜集完成后，我们可以打印其键值对，以便观察是否是我们想要的。我们可以看到这正是我们要的结果，键就是日期，值就是当天出货的所有的产品信息的嵌套列表。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-03-15 [['客户4', 'XYZ0069', 'QP006UUU00', 6]]\n",
      "2017-03-21 [['客户1', 'XYZ0038', 'XW009UUU00', 80]]\n",
      "2017-03-27 [['客户8', 'XYZ0043', 'PQ016UUU00', 22]]\n",
      "2017-04-03 [['客户3', 'XYZ0022', 'EP002UUU00', 9], ['客户3', 'XYZ0022', 'EP002UUU00', 4]]\n",
      "2017-04-08 [['客户7', 'XYZ0027', 'PUUU072UUU00', 11]]\n",
      "2017-05-05 [['客户7', 'XYZ0002', 'PUUU210UUU00', 6], ['客户7', 'XYZ0002', 'PUUU210UUU00', 8], ['客户7', 'XYZ0027', 'PUUU072UUU00', 114]]\n",
      "2017-05-06 [['客户7', 'XYZ0027', 'PUUU072UUU00', 70]]\n",
      "2017-05-10 [['客户11', 'XYZ0024', 'ST001UUU00', 140]]\n",
      "2017-05-15 [['客户3', 'XYZ0021', 'EP002UUU00', 360]]\n",
      "2017-05-19 [['客户5', 'XYZ0047', 'QE003UUU00', 8], ['客户5', 'XYZ0047', 'QE003UUU00', 11]]\n",
      "2017-05-24 [['客户12', 'XYZ0054', 'SQ149UUU00', 2], ['客户12', 'XYZ0049', 'SQ148UUU00', 21], ['客户12', 'XYZ0051', 'SQ148UUU00', 29], ['客户12', 'XYZ0050', 'SQ148UUU00', 29], ['客户12', 'XYZ0048', 'SQ148UUU00', 20], ['客户12', 'XYZ0052', 'SQ148UUU00', 21], ['客户12', 'XYZ0053', 'SQ148UUU00', 21], ['客户13', 'XYZ0072', 'TS057UUU00', 20]]\n",
      "2017-05-25 [['客户12', 'XYZ0054', 'SQ149UUU00', 22], ['客户12', 'XYZ0049', 'SQ148UUU00', 38], ['客户12', 'XYZ0051', 'SQ148UUU00', 34], ['客户12', 'XYZ0050', 'SQ148UUU00', 67], ['客户12', 'XYZ0048', 'SQ148UUU00', 27], ['客户12', 'XYZ0052', 'SQ148UUU00', 21], ['客户12', 'XYZ0053', 'SQ148UUU00', 68], ['客户14', 'XYZ0020', '28750UUU03', 200]]\n",
      "2017-05-31 [['客户2', 'XYZ0076', 'XUUU043UUU00', 60]]\n",
      "2017-06-05 [['客户12', 'XYZ0056', 'SQ147UUU00', 25], ['客户12', 'XYZ0057', 'SQ146UUU00', 155]]\n",
      "2017-06-08 [['客户6', 'XYZ0046', 'RQ011UUU00', 15], ['客户6', 'XYZ0046', 'RQ011UUU00', 33]]\n",
      "2017-06-12 [['客户7', 'XYZ0003', 'PUUU210UUU00', 19], ['客户7', 'XYZ0003', 'PUUU210UUU00', 36], ['客户7', 'XYZ0002', 'PUUU210UUU00', 31], ['客户7', 'XYZ0002', 'PUUU210UUU00', 70], ['客户7', 'XYZ0001', 'PUUU206UUU00', 14], ['客户7', 'XYZ0004', 'PUUU047UUU00', 5], ['客户7', 'XYZ0004', 'PUUU047UUU00', 20], ['客户7', 'XYZ0004', 'PUUU047UUU00', 25], ['客户7', 'XYZ0004', 'PUUU047UUU00', 30], ['客户7', 'XYZ0005', 'PUUU047UUU00', 10], ['客户7', 'XYZ0005', 'PUUU047UUU00', 20], ['客户9', 'XYZ0068', 'SH018UUU00', 25], ['客户9', 'XYZ0068', 'SH018UUU00', 67]]\n",
      "2017-06-28 [['客户2', 'XYZ0076', 'XUUU043UUU00', 46]]\n",
      "2017-07-12 [['客户2', 'XYZ0075', 'XUUU044UUU00', 20], ['客户2', 'XYZ0076', 'XUUU043UUU00', 20]]\n",
      "2017-07-14 [['客户13', 'XYZ0074', 'TS057UUU00', 10], ['客户13', 'XYZ0073', 'TS057UUU00', 10], ['客户13', 'XYZ0073', 'TS057UUU00', 20], ['客户1', 'XYZ0040', 'XW011UUU00', 30]]\n",
      "2017-07-21 [['客户7', 'XYZ0007', 'PUUU219UUU00', 9], ['客户7', 'XYZ0006', 'PUUU218UUU00', 6]]\n",
      "2017-07-27 [['客户3', 'XYZ0023', 'EP002UUU00', 67]]\n",
      "2017-08-01 [['客户2', 'XYZ0076', 'XUUU043UUU00', 49]]\n",
      "2017-08-04 [['客户7', 'XYZ0026', 'PUUU077UUU00', 50], ['客户7', 'XYZ0025', 'PUUU077UUU00', 50]]\n",
      "2017-08-19 [['客户5', 'XYZ0047', 'QE003UUU00', 9], ['客户5', 'XYZ0047', 'QE003UUU00', 14]]\n",
      "2017-08-21 [['客户7', 'XYZ0004', 'PUUU047UUU00', 30], ['客户7', 'XYZ0005', 'PUUU047UUU00', 100]]\n",
      "2017-08-24 [['客户12', 'XYZ0050', 'SQ148UUU00', 169], ['客户12', 'XYZ0053', 'SQ148UUU00', 89]]\n",
      "2017-08-25 [['客户12', 'XYZ0049', 'SQ148UUU00', 103], ['客户12', 'XYZ0051', 'SQ148UUU00', 314], ['客户12', 'XYZ0048', 'SQ148UUU00', 133], ['客户12', 'XYZ0052', 'SQ148UUU00', 105]]\n",
      "2017-09-01 [['客户12', 'XYZ0050', 'SQ148UUU00', 24]]\n",
      "2017-09-07 [['客户16', 'XYZ0034', 'YUUU045UUU00', 40], ['客户16', 'XYZ0033', 'YUUU045UUU00', 40], ['客户16', 'XYZ0032', 'YUUU045UUU00', 20], ['客户16', 'XYZ0031', 'YUUU045UUU00', 20], ['客户2', 'XYZ0075', 'XUUU044UUU00', 10], ['客户2', 'XYZ0075', 'XUUU044UUU00', 20], ['客户2', 'XYZ0075', 'XUUU044UUU00', 40], ['客户2', 'XYZ0075', 'XUUU044UUU00', 7], ['客户2', 'XYZ0076', 'XUUU043UUU00', 10], ['客户2', 'XYZ0076', 'XUUU043UUU00', 50], ['客户2', 'XYZ0076', 'XUUU043UUU00', 10]]\n",
      "2017-09-11 [['客户12', 'XYZ0059', 'SQ151UUU00', 35]]\n",
      "2017-09-19 [['客户10', 'XYZ0014', 'ST002UUU00', 20], ['客户10', 'XYZ0014', 'ST002UUU00', 80]]\n",
      "2017-09-26 [['客户15', 'XYZ0045', 'TH002UUU00', 200]]\n",
      "2017-09-27 [['客户14', 'XYZ0020', '28750UUU03', 400]]\n",
      "2017-09-28 [['客户2', 'XYZ0011', 'XUUU002UUU00', 5], ['客户2', 'XYZ0011', 'XUUU002UUU00', 5], ['客户2', 'XYZ0011', 'XUUU002UUU00', 10], ['客户2', 'XYZ0010', 'XUUU002UUU00', 20], ['客户2', 'XYZ0013', 'XUUU002UUU00', 5], ['客户2', 'XYZ0013', 'XUUU002UUU00', 5], ['客户2', 'XYZ0013', 'XUUU002UUU00', 13], ['客户2', 'XYZ0012', 'XUUU002UUU00', 5], ['客户2', 'XYZ0012', 'XUUU002UUU00', 5], ['客户2', 'XYZ0012', 'XUUU002UUU00', 5], ['客户2', 'XYZ0076', 'XUUU043UUU00', 100]]\n",
      "2017-09-29 [['客户12', 'XYZ0067', 'SQ145UUU00', 40]]\n",
      "2017-10-09 [['客户12', 'XYZ0049', 'SQ148UUU00', 63], ['客户12', 'XYZ0051', 'SQ148UUU00', 140], ['客户12', 'XYZ0050', 'SQ148UUU00', 152], ['客户12', 'XYZ0048', 'SQ148UUU00', 67], ['客户12', 'XYZ0052', 'SQ148UUU00', 34], ['客户12', 'XYZ0053', 'SQ148UUU00', 152]]\n",
      "2017-10-11 [['客户8', 'XYZ0044', 'PQ032UUU00', 24]]\n",
      "2017-10-16 [['客户12', 'XYZ0054', 'SQ149UUU00', 40], ['客户12', 'XYZ0070', 'SQ153UUU00', 36], ['客户1', 'XYZ0041', 'XW011UUU00', 30], ['客户12', 'XYZ0064', 'SQ127UUU00', 3], ['客户12', 'XYZ0061', 'SQ127UUU00', 3], ['客户12', 'XYZ0061', 'SQ127UUU00', 12], ['客户12', 'XYZ0065', 'SQ127UUU00', 3], ['客户12', 'XYZ0065', 'SQ127UUU00', 13], ['客户12', 'XYZ0066', 'SQ127UUU00', 3], ['客户12', 'XYZ0063', 'SQ127UUU00', 3], ['客户12', 'XYZ0062', 'SQ127UUU00', 3], ['客户12', 'XYZ0062', 'SQ127UUU00', 6]]\n",
      "2017-10-18 [['客户12', 'XYZ0054', 'SQ149UUU00', 23], ['客户12', 'XYZ0054', 'SQ149UUU00', 60], ['客户12', 'XYZ0054', 'SQ149UUU00', 116]]\n",
      "2017-10-19 [['客户9', 'XYZ0018', 'SH007UUU00', 7], ['客户9', 'XYZ0018', 'SH007UUU00', 18]]\n",
      "2017-10-20 [['客户7', 'XYZ0009', 'PUUU221UUU00', 3], ['客户7', 'XYZ0008', 'PUUU220UUU00', 3], ['客户7', 'XYZ0007', 'PUUU219UUU00', 3], ['客户7', 'XYZ0006', 'PUUU218UUU00', 3]]\n",
      "2017-10-21 [['客户16', 'XYZ0035', 'YUUU046UUU00', 20]]\n",
      "2017-10-25 [['客户2', 'XYZ0075', 'XUUU044UUU00', 30], ['客户2', 'XYZ0075', 'XUUU044UUU00', 30], ['客户2', 'XYZ0077', 'XUUU043UUU00', 35], ['客户2', 'XYZ0076', 'XUUU043UUU00', 50]]\n",
      "2017-10-26 [['客户2', 'XYZ0077', 'XUUU043UUU00', 25], ['客户2', 'XYZ0077', 'XUUU043UUU00', 50]]\n",
      "2017-10-28 [['客户12', 'XYZ0019', 'SQ150UUU00', 25]]\n",
      "2017-11-01 [['客户15', 'XYZ0045', 'TH002UUU00', 200]]\n",
      "2017-11-02 [['客户7', 'XYZ0028', 'PUUU217UUU00', 21]]\n",
      "2017-11-04 [['客户12', 'XYZ0060', 'SQ145UUU00', 15], ['客户12', 'XYZ0060', 'SQ145UUU00', 137]]\n",
      "2017-11-06 [['客户1', 'XYZ0041', 'XW011UUU00', 10], ['客户1', 'XYZ0040', 'XW011UUU00', 10], ['客户1', 'XYZ0042', 'XW002UUU00', 10], ['客户12', 'XYZ0019', 'SQ150UUU00', 50], ['客户7', 'XYZ0030', 'PUUU114UUU00', 4]]\n",
      "2017-11-10 [['客户2', 'XYZ0071', 'XUUU041UUU00', 10], ['客户10', 'XYZ0014', 'ST002UUU00', 70]]\n",
      "2017-11-11 [['客户16', 'XYZ0037', 'YUUU046UUU00', 15], ['客户16', 'XYZ0035', 'YUUU046UUU00', 15], ['客户16', 'XYZ0036', 'YUUU046UUU00', 15]]\n",
      "2017-11-13 [['客户16', 'XYZ0032', 'YUUU045UUU00', 16], ['客户16', 'XYZ0031', 'YUUU045UUU00', 12]]\n",
      "2017-11-18 [['客户12', 'XYZ0058', 'SQ146UUU00', 5], ['客户12', 'XYZ0058', 'SQ146UUU00', 10]]\n",
      "2017-11-20 [['客户12', 'XYZ0055', 'SQ074UUU00', 20]]\n",
      "2017-11-30 [['客户12', 'XYZ0054', 'SQ149UUU00', 100], ['客户12', 'XYZ0070', 'SQ153UUU00', 10]]\n",
      "2017-12-01 [['客户2', 'XYZ0017', 'XUUU035UUU00', 5]]\n",
      "2017-12-02 [['客户12', 'XYZ0054', 'SQ149UUU00', 130]]\n",
      "2017-12-04 [['客户16', 'XYZ0035', 'YUUU046UUU00', 20], ['客户16', 'XYZ0036', 'YUUU046UUU00', 100]]\n",
      "2017-12-05 [['客户16', 'XYZ0037', 'YUUU046UUU00', 50], ['客户16', 'XYZ0035', 'YUUU046UUU00', 105]]\n",
      "2017-12-06 [['客户2', 'XYZ0015', 'XUUU042UUU00', 14], ['客户2', 'XYZ0016', 'XUUU042UUU00', 12]]\n",
      "2017-12-13 [['客户15', 'XYZ0045', 'TH002UUU00', 100]]\n",
      "2017-12-18 [['客户12', 'XYZ0058', 'SQ146UUU00', 250], ['客户7', 'XYZ0029', 'PUUU217UUU00', 7], ['客户7', 'XYZ0029', 'PUUU217UUU00', 6], ['客户7', 'XYZ0028', 'PUUU217UUU00', 20], ['客户7', 'XYZ0030', 'PUUU114UUU00', 25], ['客户7', 'XYZ0030', 'PUUU114UUU00', 20], ['客户7', 'XYZ0030', 'PUUU114UUU00', 81]]\n",
      "2017-12-19 [['客户7', 'XYZ0028', 'PUUU217UUU00', 28], ['客户7', 'XYZ0028', 'PUUU217UUU00', 29]]\n",
      "2017-12-23 [['客户1', 'XYZ0039', 'XW020UUU00', 5]]\n"
     ]
    }
   ],
   "source": [
    "for key,value in data.items(): #打印键值对，以便观察\n",
    "    print(key,value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据获取完成后，就可以开始创建并写入每日出货清单了。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2.将提取的信息按日期写入新建的Excel表\n",
    "wb_day = load_workbook(\"data\\出货单模板.xlsx\")\n",
    "ws_day= wb_day['temp']\n",
    "for date in data.keys():\n",
    "    ws_new = wb_day.copy_worksheet(ws_day) #复制模板中的工作表\n",
    "    ws_new.title=str(date)[-5:] #以日期为新工作表命名\n",
    "    ws_new.cell(row=3,column=5).value=date #E3单元格固定填写日期\n",
    "    i=5 #计数器，从第5行开始填写起始值为5\n",
    "    for product in data[date]: #获取每天出货的每个产品信息，逐个写入工作表\n",
    "        ws_new.cell(row=i,column=2).value=product[0]\n",
    "        ws_new.cell(row=i,column=3).value=product[1]\n",
    "        ws_new.cell(row=i,column=4).value=product[2]\n",
    "        ws_new.cell(row=i,column=5).value=product[3]\n",
    "        i+=1 #每写一行，计数器就需要加1，以便从下一行接着写入\n",
    "wb_day.save(\"data\\出货单.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们事先设置好了一个产品信息为空的Excel模板，随后让程序将每天的出货信息填入即可。先打开模板“出货单模板.xlsx”，然后获取其工作表“temp”，以便后续复制并写入数据。我们建立一个`for`循环，遍历字典`data`里面的所有的键（即日期）。使用`copy_worksheet`复制模板工作表，存入`ws_new`，并将其标题重命名为日期`ws_new.title`。E3单元格是填入固定的值，即日期，所以直接赋值为`date`。因为每天可能有2个及以上的出货信息，那就需要在出货清单中填写几行信息，所以需要设置一个行计数器i，其初始值为5，因为出货清单是从第5行开始的。每填完一行信息，计数器就加1`i+=1`，然后就可以填写下一行了。\n",
    "\n",
    "所有信息填写完后，就保存数据`wb_day.save(\"data\\出货单.xlsx\")`，任务完成。几百个工作表瞬间填完，结果如下图：\n",
    "![](images\\result.png)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
